import os
from sqlalchemy.engine.url import URL
import logging
from PyQt5.QtCore import QThread, pyqtSignal, QObject
import re
import winreg
import datetime, time
from DBUtils.pooled_db import PooledDB
import pyodbc
import random
import pymysql
from datetime import datetime
from sqlalchemy import create_engine, false, table
from sqlalchemy.orm import sessionmaker
import logging
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
import asyncio
from pyppeteer import launch
import re

dbconnection_mysql = f"mysql+pymysql://root:123456@gz-cdb-pr3j7lhn.sql.tencentcdb.com:26590/amazon"
dbconnection_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=amazon;UID=sa;PWD=123456}"
from selenium.webdriver.common.keys import Keys
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.by import By
from PyQt5.QtCore import QAbstractTableModel, Qt
from selenium import webdriver
from selenium.webdriver.common.action_chains import ActionChains
import winreg


def is_floatstr(string):
    pattern = re.compile(r'^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0 - 9]+)?$')
    return pattern.fullmatch(string) is not None


user_agents = [
    "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 10.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 5.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 11.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
]

def has_ip_address(text):
    ip_pattern = r'\b(?:\d{1,3}\.){3}\d{1,3}\b'
    return bool(re.search(ip_pattern, text))


def extract_float(s):
    match = re.search(r'\d+\.\d+', s)
    if match:
        return match.group(1)
    else:
        return "未找到匹配的内容"


def generate_random_ua():
    return random.choice(user_agents)


def get_chrome_path_windows():
    try:
        key = winreg.OpenKey(
            winreg.HKEY_LOCAL_MACHINE,
            r"SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\chrome.exe")
        value, _ = winreg.QueryValueEx(key, None)
        return value
    except FileNotFoundError:
        return None


class DatabaseModel(QAbstractTableModel):

    def __init__(self, data):
        super().__init__()
        self._data = data
        self.column_names = []

    def setcolumns(self, _columns):
        self.column_names = _columns

    def rowCount(self, parent=None):
        return len(self._data)

    def columnCount(self, parent=None):
        return len(self._data[0]) if self._data else 0

    def data(self, index, role=Qt.DisplayRole):
        if role == Qt.DisplayRole:
            return self._data[index.row()][index.column()]
        return None

    def headerData(self, section, orientation, role=Qt.DisplayRole):
        if role == Qt.DisplayRole and orientation == Qt.Horizontal and not self.column_names is None and len(
                self.column_names) > 0:
            return self.column_names[section]
        return super().headerData(section, orientation, role)


class dbhelper:

    def __init__(self,
                 creator=None,
                 driver=None,
                 server=None,
                 database=None,
                 uid=None,
                 pwd=None,
                 port=None):
        try:
            # 创建连接池
            if creator is None:

                driver = '{ODBC Driver 17 for SQL Server}'
            if server is None:
                server = 'localhost'
            if database is None:
                database = 'amazon'
            if uid is None:
                uid = 'sa'
            if pwd is None:
                pwd = '123456'
            if port is None:
                port = 3306
            # self.pool = PooledDB(creator=creator,
            #                      mincached=2,
            #                      maxcached=5,
            #                      maxconnections=100,
            #                      blocking=True,
            #                      server=server,
            #                      port=port,
            #                      database=database,
            #                      uid=uid,
            #                      pwd=pwd)
            if creator is None:
                # 数据库类型为：ms sql server
                self.pool = PooledDB(mincached=2,
                                     maxcached=5,
                                     maxconnections=100,
                                     blocking=True,
                                     driver=driver,
                                     host=server,
                                     port=port,
                                     database=database,
                                     uid=uid,
                                     pwd=pwd,
                                     creator=pyodbc)
            else:
                # 数据库类型为：mysql
                self.pool = PooledDB(creator=creator,
                                     mincached=2,
                                     maxcached=5,
                                     maxconnections=20,
                                     blocking=True,
                                     host=server,
                                     port=port,
                                     database=database,
                                     user=uid,
                                     charset='utf8mb4',
                                     password=pwd)
        except Exception as e:
            print(e)
        finally:
            print('dbconection')

    def get_connection(self):
        return self.pool.connection()

    def close_connection(self, conn):
        conn.close()

    def dbread_data(self, sql):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(sql)
        rows = cursor.fetchall()
        self.close_connection(conn)
        return rows

    def dictinsert(self, table_name, data_dict):
        # 字典数组插入数据库
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            for item in data_dict:
                columns = ', '.join(item.keys())
                placeholders = ''
                for val in item.values():
                    placeholders += f"'{val}',"
                placeholders = placeholders[:-1]
                insert_query = f"INSERT INTO {table_name} ({columns}) select   {placeholders} "
                values = tuple(item.values())
                # cursor.execute(insert_query, values)
                cursor.execute(insert_query)
            conn.commit()
        except pymysql.Error as e:
            print(f"Error inserting data: {e}")
            conn.rollback()
        finally:
            self.close_connection(conn)

    def dbexecute(self, sql):
        conn = self.get_connection()
        cursor = conn.cursor()

        cursor.execute(sql)
        introws = cursor.rowcount

        conn.commit()
        self.close_connection(conn)
        return introws

    def dbexecutemany(self, sql, data):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(sql, data)
        introws = cursor.rowcount
        conn.commit()
        self.close_connection(conn)
        return introws


def getconnection_url():
    connection_params = {
        "drivername": "mssql+pyodbc",
        "username": "sa",
        "password": "123456",
        "host": "127.0.0.1",  # 或者是本地的 IP 地址，如果是默认实例通常是 localhost
        "port": 1433,  # 默认端口通常是 1433，如果有修改则使用修改后的端口
        "database": "amazon",
        "query": {
            "driver": "ODBC Driver 17 for SQL Server"
        }
    }
    connection_url = URL.create(**connection_params)
    return connection_url


def getbrandinfo():
    current_path = os.getcwd()
    fileaccounmt = os.path.join(current_path + "\\trademark.txt")
    logging.info(f'读取访问配置文件，配置文件存放于{fileaccounmt}\n')
    account_info = {}
    try:
        with open(fileaccounmt, 'r') as file:
            lines = file.readlines()
            for line in lines:
                line = line.strip()
                if line and '=' in line:
                    key, value = line.split('=')
                    account_info[key.strip()] = value.strip()
                    value = value.strip()
                    if ' ' in value:
                        account_info[key.strip()] = value.split(' ')[0].strip()

    except FileNotFoundError:
        logging.info(f'读取访问配置文件失败！，配置文件存放于{fileaccounmt}\n')

    finally:
        return account_info


def gettablename(table_name):
    table_name2 = re.findall(r'[a-zA-Z_]+', table_name)
    combined_result = "".join(table_name2)
    return combined_result


def getnumalpha(str):
    result = re.findall(r'[a-zA-Z0-9]', str)
    return ''.join(result)


def write_log(con, data, text):
    # 用文本框记录日志信息
    # 数据库也写信息
    logging.info(f'{data}\n')
    sql = f"insert into us_logg(disknumber,username,ip,opdate,gateway,memo,action) values('{data['disknumber']}','{data['username']}','{data['ip']}',{data['datefunction']},'{data['gateway']}','{data['memo']}','{data['action']}')"
    con.dbexecute(sql)
    if not text is None:
        text.append(data)


def getaccountinfo():
    current_path = os.getcwd()
    fileaccounmt = os.path.join(current_path + "\\account.txt")
    logging.info(f'读取访问配置文件，配置文件存放于{fileaccounmt}\n')
    account_info = {}
    try:
        with open(fileaccounmt, 'r') as file:
            lines = file.readlines()
            for line in lines:
                line = line.strip()
                if line and '=' in line:
                    key, value = line.split('=')
                    account_info[key.strip()] = value.strip()
                    value = value.strip()
                    if ' ' in value:
                        account_info[key.strip()] = value.split(' ')[0].strip()

    except FileNotFoundError:
        logging.info(f'读取访问配置文件失败！，配置文件存放于{fileaccounmt}\n')

    finally:
        return account_info


def parse_biaoju_us2(dbo):
    #下载美国商标，根据指令下载
    now = datetime.now()
    current_time = now.strftime("%Y-%m-%d %H:%M:%S")
    driver = None
    for k in range(5):
        logging.info('')

    logging.info(
        f'开始记录日志时间：{current_time}，技术支持：492524133@qq.com,加vx:yiliyamei注明sif分析.谢谢'
    )

    headers = {
        'authority':
        'identify.tankeai.com',
        'method':
        'POST',
        'path':
        'mtc/pageview',
        'scheme':
        'https',
        'Accept':
        '*/*',
        'Accept-Encoding':
        'gzip, deflate, br, zstd',
        'Accept-Language':
        'zh-CN,zh;q=0.9',
        'Origin':
        'https//www.biaoju01.com',
        'Priority':
        'u=1, i',
        'Referer':
        'https//www.biaoju01.com/',
        'Sec-Ch-Ua':
        '"Not/A)Brand";v="8", "Chromium";v="126", "Google Chrome";v="126"',
        'Sec-Ch-Ua-Mobile':
        '?0',
        'Sec-Ch-Ua-Platform':
        "Windows",
        'Sec-Fetch-Dest':
        'empty',
        'Sec-Fetch-Mode':
        'cors',
        'Sec-Fetch-Site':
        'cross-site',
        'User-Agent':
        'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36'
    }

    current_path = os.getcwd()
    fileaccounmt = os.path.join(current_path + "\\trademark.txt")
    logging.info(f'读取访问sif.com的账号密码配置文件，存放于{fileaccounmt}\n')
    account_info = {}
    engine = None
    session = None
    engine = create_engine(
        'mssql+pyodbc://sa:123456@127.0.0.1/amazon?driver=ODBC+Driver+17+for+SQL+Server'
    )

    connection = engine.connect()
    Session = sessionmaker(bind=engine)
    session = Session()

    account_info = getbrandinfo()

    # sql=f" select * from trademark where 1=2"
    # result=session.execute(text(sql))
    # columns = [col[0] for col in result.cursor.description]
    # asinstore_index = columns.index('卖家首页')
    # weighcol_index= columns.index('重量one')
    intuseself = 1
    intdebug = 0
    intpopres = 0
    ver1 = get_chrome_version()

    try:
        chrome_options = Options()
        chrome_options.add_argument('--disable-dev-shm-usage')
        # 创建Chrome浏览器实例
        if intuseself == 0:
            chrome_options.add_argument("--headless")  # 添加无头模式参数
        chrome_options.add_argument("--no-sandbox")
        chrome_options.add_experimental_option('excludeSwitches',
                                               ['enable-automation'])
        chrome_options.add_argument('--ignore-certificate-errors')
        chrome_options.add_argument('--ignore-ssl-errors')

        # 创建一个webdriver实例
        chrome_options.add_argument('--disable-gpu-vsync')
        current_path = os.getcwd()
        # 设置代理
        #proxy2=ipdatalist[random.randint(0,len(ipdatalist)-1)]
        #chrome_options.add_argument(f'--proxy-server={proxy2}')
        chromedriver_path = os.path.join(current_path, "chromedriver.exe")
        logging.info(f'chromedriver_path={chromedriver_path}')

        # 创建 Service 对象并传入路径
        service = Service(executable_path=chromedriver_path)

        # 创建 WebDriver 实例时传入 Service 对象和 ChromeOptions
        driver = webdriver.Chrome(service=service, options=chrome_options)

        ver2 = driver.capabilities['chrome']['chromedriverVersion'].split(
            ' ')[0]

        logging.info(f'chromedriver版本{ver2}\n')
        #driver = webdriver.Chrome()
        #driver = webdriver.Chrome(options=chrome_options)
        driver.implicitly_wait(1)

        for key, value in headers.items():
            driver.execute_script(
                f"var header = document.createElement('meta'); header.name = '{key}'; header.content = '{value}'; document.getElementsByTagName('head')[0].appendChild(header);"
            )
        # 改为读取数据库
        driver.get("https://www.biaoju.cn/login")
        time.sleep(random.random() / 4)

        dict_cfg = getbrandinfo()
        savefilepath = os.getcwd()
        ibiaosleep = int(dict_cfg['usptosleep'])
        introw = int(dict_cfg['intbrandrows'])
        brandprefix = dict_cfg['makeprefixbrand']
        intappend = int(dict_cfg['intappend'])
        if not os.path.exists(savefilepath):
            os.makedirs(savefilepath)
        dict_cfg["way"] = 2
        data1 = {}
        data1["account"] = dict_cfg["biaojuuser"]
        #root.title("我的窗口-" + data1["account"])  # 设置窗口标题
        data1["secret"] = dict_cfg["biaojupassword"]
        # dbo = dbhelper()
        dict_cfg = getbrandinfo()
        savefilepath = os.getcwd()
        ibiaosleep = int(dict_cfg['usptosleep'])
        introw = int(dict_cfg['intbrandrows'])
        brandprefix = dict_cfg['makeprefixbrand']
        if not os.path.exists(savefilepath):
            os.makedirs(savefilepath)

        # 将垂直滚动条拉到最低
        # driver.execute_script(
        #     "window.scrollTo(0, document.body.scrollHeight);")
        time.sleep(random.random() / 8)
        # 将平行滚动条拉到最右侧
        # element = WebDriverWait(driver, 10).until(
        #     EC.element_to_be_clickable(
        #         (By.XPATH, '//a[@data-v-6ac962f5]')))

        # element.click()
        # time.sleep(random.random() / 8)
        # #密码登录
        # element = WebDriverWait(driver, 4).until(
        #     EC.element_to_be_clickable((
        #         By.XPATH,
        #         '//input[@class="el-input__inner" and @placeholder="请输入手机号/邮箱"]'
        #     )))
        # time.sleep(random.random())
        # # element.click()
        # # 创建 ActionChains 对象
        # element.send_keys(dict_cfg["biaojuuser"])
        # element = WebDriverWait(driver, 10).until(
        #     EC.element_to_be_clickable((
        #         By.XPATH,
        #         '//input[@class="el-input__inner" and @placeholder="请输入密码"]'
        #     )))
        # # 输入内容

        # # 模拟按下回车键
        # element.send_keys(dict_cfg["biaojupassword"])
        # element.send_keys(Keys.ENTER)
        textdd = "这是第一次访问uspto网页，搜索字符:{strkw}如果你准备好了（选择好类目，每页记录数量，是否显示图片等）!,请点击确认"
        # if intpopres == 0:
        #     reply = QMessageBox.information(None, "提示", textdd)

        #     intpopres += 1
        #     if reply == QMessageBox.No:
        #         return
        # time.sleep(1)
        sql = 'select top 5  * from  us_brands'
        rows = dbo.dbread_data(sql)

        driver.get("https://www.biaoju.cn/trademarkSearchInternational")
        driver.implicitly_wait(10)  #隐式等待10秒
        datasimilars = []
        index = 0
        time.sleep(ibiaosleep)
        for row in rows:
            index += 1
            brand = row[1]
            try:
                element = WebDriverWait(driver, 60).until(
                    EC.visibility_of_element_located(
                        (By.CSS_SELECTOR, '.el-input__inner')))
                actions = ActionChains(driver)
                actions.move_to_element(element).click().perform()
                for ix in range(13):
                    actions.move_to_element(element).send_keys(
                        Keys.BACK_SPACE).perform()
                actions.move_to_element(element).send_keys(brand).perform()
                actions.move_to_element(element).send_keys(
                    Keys.ENTER).perform()

                wait = WebDriverWait(driver, 60)
                element = wait.until(
                    EC.element_to_be_clickable(
                        (By.XPATH, "//div[text()='导出']")))
                actions = ActionChains(driver)
                actions.move_to_element(element).click().perform()
                time.sleep(3)
                continue

                print(f'brand:{brand},similarcount:{number}')
                datasimilars.append({'brand': brand, 'similarcount': number})
                if index % intappend == 0:
                    sql = 'update us_brands  set similarcount=:similarcount where brand=:brand '
                    with engine.connect() as connection:
                        connection.execute(sql, datasimilars)
                        connection.commit()
                    dbo.dbexecute(sql, datasimilars)
                    datasimilars = []
                time.sleep(ibiaosleep)
            except Exception as e:
                print(e)

        if len(datasimilars) > 0:
            sql = 'update us_brands  set similarcount=:similarcount where brand=:brand '
            with engine.connect() as connection:
                connection.execute(sql, datasimilars)
                connection.commit()

    except Exception as e:
        logging.info(f'访问uspto的出现错误：{e}\n')
        print(e)
    finally:
        # driver.quit()
        aaa = 234


def get_chrome_version():
    try:
        key = winreg.OpenKey(winreg.HKEY_CURRENT_USER,
                             r"Software\Google\Chrome\BLBeacon")
        version, _ = winreg.QueryValueEx(key, "version")
        winreg.CloseKey(key)
        return version
    except WindowsError:
        return None


def get_chrome_path():
    try:
        key = winreg.OpenKey(
            winreg.HKEY_LOCAL_MACHINE,
            r"SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\chrome.exe")
        value, _ = winreg.QueryValueEx(key, None)
        return value
    except FileNotFoundError:
        return None


async def biaoju3():
    try:
        current_path = os.getcwd()

        chromedriver_path = os.path.join(current_path, "chromedriver.exe")

        print(chromedriver_path)
        x1 = path = r'C:\Users\Administrator\AppData\Local\Google\Chrome\Application\chrome.exe'

        print(x1)
        # browser = await launch(executablePath=chromedriver_path,
        #                        args=['--disable-gpu', '--no-sandbox'])
        browser = await launch(executablePath=x1,
                               args=['--disable-gpu', '--no-sandbox'],
                               headless=False)
        page = await browser.newPage()
        await page.goto('https://www.biaoju.cn/trademarkSearchInternational')

        # await page.waitForSelector('a[data-v-6ac962f5=""]:contains("密码登录")')

        # # 获取该元素并点击
        # element = await page.querySelector(
        #     'a[data-v-6ac962f5=""]:contains("密码登录")')
        # if element:
        #     await page.evaluate('(element) => element.click()', element)
        # 获取所有的 <a> 元素
        all_links = await page.querySelectorAll('a')
        for link in all_links:
            text = await page.evaluate('(el) => el.textContent', link)
            if text and "密码登录" in text:
                # 找到包含“密码登录”的元素，进行操作
                await page.evaluate('(element) => element.click()', link)
                break
        await page.waitForSelector('input.el-input__inner[type="text"]')

        # 获取元素并输入内容
        input_element = await page.querySelector(
            'input.el-input__inner[type="text"]')
        if input_element:
            await page.evaluate('(element, value) => element.value = value',
                                input_element, '17302649548')

        await page.waitForSelector('input.el-input__inner[type="password"]')

        # 获取密码输入框元素
        password_input = await page.querySelector(
            'input.el-input__inner[type="password"]')
        if password_input:
            # 模拟输入密码
            await password_input.type('Zb@200407')

            # 等待登录按钮元素出现


# 获取所有的 div 元素
        all_divs = await page.querySelectorAll('div')
        for div in all_divs:
            text_content = await page.evaluate('(el) => el.textContent', div)
            if text_content and text_content.strip(
            ) == "登录" and await page.evaluate(
                    '(el) => el.classList.contains("form-btn")', div):
                await page.evaluate('(element) => element.click()', div)
                break

        await page.waitForSelector('.el-input__inner')
        await page.click('.el-input__inner')
        for i in range(13):
            await page.keyboard.press('Backspace')

        await page.type('#el-input__inner', 'brand')

        # // 模拟按下回车键
        await page.keyboard.press('Enter')

        await page.waitForSelector('.el-pagination__total')

        elements = await page.querySelectorAll('.el-pagination__total')
        for element in elements:
            text = await page.evaluate('(el) => el.textContent', element)
            print(f'Element with class "target-class" text: {text}')

        await page.pdf({path: 'output.pdf', format: 'A4'})
        html = await page.content()
        fs.writeFileSync('output.html', html)

        await browser.close()
    except Exception as e:
        print(e)


async def main():
    await biaoju3()


if __name__ == '__main__':
    asyncio.run(main())

# if __name__ == '__main__':

#     logging.basicConfig(filename='xmapp.log',
#                         level=logging.DEBUG,
#                         format='%(asctime)s - %(levelname)s - %(message)s')

#     logging.debug('这是一条调试日志，写入到文件中')
#     dbhelper = dbhelper()

#     rows = dbhelper.dbread_data('select 1')
#     print(rows)
#     x = dbhelper.dbexecute('update us_brands set code =1 where 1=2')
#     print(x)
#     parse_biaoju_us2(dbhelper)
#     data1 = {'a': 1, 'b': 2}
#     data2 = {'a': 3, 'b': 4}
#     data = []
#     data.append(data1)
#     data.append(data2)
#     sql = 'insert into  us_brands(code,brand)   select a:,:b from us_brands where 1=2'
#     # y = dbhelper.dbexecutemany(sql, data)
#     # print(y)
#     sql = 'update us_brands set code =? +? where 1=2'
#     data = [1, 2]
#     y = dbhelper.dbexecutemany(sql, data)
#     print(y)
